
ALTER  proc sp_load_die_bank_lots(
@product_name varchar(30)
)
as
begin
	declare @results Table(Lot_ID bigint,
				Part_Number varchar(30),
				Lot_Number varchar(30),
				Bin_1 int,
				Bin_2 int)

	/**
	* This procedure will load the die bank lots for particular product.
	* Algorithm
	*	1) Collect the die bank lots using part number and store it into CURSOR
	*	2) Bring the Sort Process step id to load the bin information
	*/
	declare @lot_id bigint
	declare @lot_number varchar(30)
	declare @part_number varchar(30)
	declare @bin_1 int
	declare @bin_2 int
	declare @process_step_id bigint
	declare @sort_process_step_id bigint

	declare @lot_cursor CURSOR 

	set @lot_cursor = CURSOR for
	select Product.Part_Number, Lot_Table.ProcessStep_ID, Lot_Number from Lot_Table, Product, Process_Step where Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID and Process_Step.ProcessStep_ID = (select ProcessStep_ID from fn_get_process_flow( @product_name) where Process_Type_Name = 'Die Bank') and Process_Step.ProcessStep_ID = Product.ProcessStep_ID and Lot_Table.Lot_Current_Status = 'Running'

	open @lot_cursor

		fetch next from @lot_cursor 
		into @part_number, @process_step_id, @lot_number

		while @@fetch_status = 0
		begin
			select @sort_process_step_id = ProcessStep_ID from Process_Step_Traverse_Table where Next_ProcessStep_ID = @process_step_id
			select @lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @sort_process_step_id and Lot_Number = @lot_number
			select @bin_1 = sum(Bin_1), @bin_2 = sum(bin_2) from Informative_Binning where Lot_ID = @lot_id

			insert into @results values(@lot_id, @part_number, @lot_number, @bin_1, @bin_2)

			set @lot_id = 0
			set @lot_number = ''
			set @bin_1 = 0
			set @bin_2 = 0

			fetch next from @lot_cursor 
			into @part_number, @process_step_id, @lot_number
		end
	close @lot_cursor
	deallocate @lot_cursor

	select *from @results
	return
end

